package sql;

/*import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import util.DBHelper;
import bean.student;
import bean.library;
import bean.studentLibrary;
*/
import java.sql.*;

public class sqlConnect {
	private static final String driver = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://127.0.0.1:3306/yu";
	private static final String username = "root";
	private static final String password = "dys182182";
	private static Connection con = null;
	//静态代码块负责加载驱动
		static
		{
			try
			{
				Class.forName(driver);
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
			}
		}
		//单例模式返回数据库连接对象
		public static Connection getConnection() throws Exception
		{
			if(con == null)
			{
				con = DriverManager.getConnection(URL,username,password);
				return con;
			}
			return con;
		}
		public static void main(String[] args){
			try
			{
				Connection conn = sqlConnect.getConnection();
				if(conn != null)
				{
					System.out.println("数据库连接正常");
				}
				else
				{
					System.out.println("连接数据库异常");
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
			}
		}
	//连接数据库
	/*public sqlConnect(){
		try {
			Class.forName(driver).newInstance();
			con = DriverManager.getConnection(URL,username,password);
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			System.out.println("找不到数据库连接驱动！");
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	//输入学生注册信息
	public void insertStudent(student student){
		String sqlStr = "insert into student(student_num,name,sex,grade,profession,username,password)" +
				" values ('" + student.getStudentNum() + "','" + student.getName() + "'," +student.getSex()+ "," + student.getGrade() + 
				",'"+student.getProfession()+"','"+student.getUsername()+"','"+student.getPassword()+"'"+");";
		System.out.println(sqlStr);
		try {
			
			PreparedStatement pre = con.prepareStatement(sqlStr);
			pre.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	//输入借书信息
	public void insertLibrary(library library){
		String sqlStr = "insert into student" +
				" values ('" + library.getBookID()+ "','" + library.getBookName() + "','" +
				 library.getborrowTime()+ "','" + library.getReturnTime() + "','" + library.getPicture()+ "');";
		System.out.println(sqlStr);
		try {
			
			PreparedStatement pre = con.prepareStatement(sqlStr);
			pre.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	//获得学生信息
	public List<student> selectAllStudent (){
		String sqlStr = "select * from student where 1=1";
		try {
			PreparedStatement ps = con.prepareStatement(sqlStr);
			ResultSet rs = ps.executeQuery();
			List<student> studentList = new ArrayList<student>();
			while(rs.next()){
				student student = new student();
				student.setStudentNum(rs.getString(1));
				student.setName(rs.getString(2));
				student.setSex(rs.getInt(3));
				student.setGrade(rs.getInt(4));
				student.setProfession(rs.getString(5));
				student.setUsername(rs.getString(6));
				student.setPassword(rs.getString(7));
				studentList.add(student);
			}
			return studentList;
		} catch (SQLException e) {
			System.out.println("查无结果");
			e.printStackTrace();
			return null;
		}	
	}
	//获得借书信息
	public List<library> selectAllLibrery (){
		String SqlStr = "select * from library where 1=1";
		try{
			PreparedStatement ps = con.prepareStatement(SqlStr);
			ResultSet rs = ps.executeQuery();
			List<library> libraryList = new ArrayList<library>();
			while(rs.next()){
				library library = new library();
				library.setBookID(rs.getString(1));
				library.setBookName(rs.getString(2));
				library.setBorrowTime(rs.getString(3));
				library.setReturnTime(rs.getString(4));
				library.setPicture(rs.getString(5));
				libraryList.add(library);
			}
			return  libraryList;
		}catch (SQLException e){
			System.out.println("查无结果");
			e.printStackTrace();
			return null;
		}
	}*/
}

